﻿using System;
using System.Collections.Generic;
using System.Text;
using ColdSun.Commons.Entities;
using Microsoft.Office.Interop.Excel;
using System.IO;
using ColdSun.Commons;
using ColdSun.Commons.Configs;

namespace ColdSun.Services
{
    public class UpdateAddressService
    {
        public string UpdateAddress(
            string originalFile, Replacement replacementInfo, string updateColumns,
            int headerRowIndex, bool isSaveAs, string exportFile, string exportFolder)
        {
            Worksheet originalWorksheet = null;
            Workbook originalWorkbook = null;
            Microsoft.Office.Interop.Excel.Application excelApplication = null;
            try
            {
                if (!File.Exists(originalFile))
                {
                    return string.Format("Can't find the source file {0}", originalFile);
                }

                excelApplication = new Microsoft.Office.Interop.Excel.Application();
                originalWorkbook = excelApplication.Workbooks.Open(originalFile);
                originalWorksheet = (Worksheet)originalWorkbook.Worksheets.get_Item(1);

                int originalRowIndex = headerRowIndex + 1;

                while (true)
                {
                    object currentValue = GetValue(originalWorksheet, updateColumns, originalRowIndex);
                    object nextValue = GetValue(originalWorksheet, updateColumns, originalRowIndex + 1);
                    if (string.IsNullOrEmpty(currentValue.ToString()) && string.IsNullOrEmpty(nextValue.ToString()))
                    {
                        originalRowIndex++;
                        break;
                    }

                    string updateValue = UpdateAddress(replacementInfo, currentValue.ToString());
                    PasteValue(originalWorksheet, updateColumns, originalRowIndex, updateValue);
                    originalRowIndex++;
                }


                if (isSaveAs)
                {
                    string saveFile = string.Format("{0}\\{1}", exportFolder, exportFile);
                    saveFile = saveFile.Replace("#Date#", DateTime.Now.ToString("yyyyMMdd hhmm"));
                    originalWorkbook.SaveAs(saveFile);
                }
                else
                {
                    originalWorkbook.Save();
                }
                return "Successful";
            }
            catch (Exception ex)
            {
                return ex.Message + ex.StackTrace;
            }
            finally
            {
                ReleaseComObjectService.ReleaseComObject(originalWorksheet);
                ReleaseComObjectService.ReleaseWorkbookObject(originalWorkbook, isSaveAs);
                ReleaseComObjectService.ReleaseExcelApplicationObject(excelApplication);

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        private void CopyValue(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex, Worksheet destWorksheet, string destColumnName, int destRowIndex)
        {
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value;
        }

        private object GetValue(Worksheet sourceWorksheet, string sourceColumnName, int sourceRowIndex)
        {
            return string.Format("{0}", sourceWorksheet.get_Range(Utilities.Cell(sourceColumnName, sourceRowIndex)).Value);
        }

        private void PasteValue(Worksheet destWorksheet, string destColumnName, int destRowIndex, object value)
        {
            destWorksheet.get_Range(Utilities.Cell(destColumnName, destRowIndex)).Value = value;
        }

        private string UpdateAddress(string sourceText)
        {
            if (string.IsNullOrEmpty(sourceText)) return "";            

            string result = sourceText.Trim().Replace("-", ",");
            result = result.Replace(".", " ");
            result = result.Replace("  ", " ");
            result = result.Replace(" ,", ",");
            result = result.Replace(", ", ",");
            result = result.Replace(",", ", ");
            result = result.Trim();
            for (int number = 0; number < 10; number++)
            {
                result = result.Replace(string.Format("{0} ", number), string.Format("{0}, ", number));
            }

            if (result.EndsWith("TPCT"))
            {
                result = result.Substring(0, result.LastIndexOf("TPCT")) + "CAN THO";
            }

            if (result.EndsWith("KH"))
            {
                result = result.Substring(0, result.LastIndexOf("KH")) + "KHANH HOA";
            }

            if (result.EndsWith("DN"))
            {
                result = result.Replace("DN", "DONG NAI");
            }

            result = result.Replace("LCHIEU", "LIEN CHIEU");
            result = result.Replace("HKHANH BAC", "HOA KHANH BAC");
            result = result.Replace("BA DON ", "BA DON, ");
            result = result.Replace("QUANG TRACH ", "QUANG TRACH, ");
            result = result.Replace("HOA CUONG BAC ", "HOA CUONG BAC, ");
            result = UpdateStringWithCondition(", P ", result);
            if (result.Contains(", PHUONG SAI,") || result.Contains(", PHUONG SON,"))
            {
                result = UpdateStringWithCondition(", PHUONG ", result);
            }
            result = UpdateStringWithCondition(", Q ", result);
            result = UpdateStringWithCondition(", QUAN ", result);
            result = UpdateStringWithCondition(", H ", result);
            result = UpdateStringWithCondition(", HUYEN ", result);
            result = UpdateStringWithCondition(", T ", result);
            result = UpdateStringWithCondition(", TINH ", result);
            result = UpdateStringWithCondition(", TP ", result);
            result = UpdateStringWithCondition(", THANH PHO ", result);

            

            return result;
        }

        private string UpdateAddress(Replacement replacementInfo, string sourceText)
        {
            if (string.IsNullOrEmpty(sourceText)) return "";

            string result = sourceText.Trim().Replace("-", ",");
            result = result.Replace(".", " ");
            result = result.Replace("  ", " ");
            result = result.Replace(" ,", ",");
            result = result.Replace(", ", ",");
            result = result.Replace(",", ", ");
            result = result.Trim();
            for (int number = 0; number < 10; number++)
            {
                result = result.Replace(string.Format("{0} ", number), string.Format("{0}, ", number));
            }

            foreach (ReplaceTextDetail detail in replacementInfo.ReplaceTextDetails)
            {
                bool isExceluded = false;

                if (!string.IsNullOrEmpty(detail.ExcludedTexts))
                {
                    foreach (string excludedText in detail.ExcludedTexts.Split(';'))
                    {
                        if (detail.SourceText.Contains(excludedText))
                        {
                            isExceluded = true;
                            break;
                        }
                    }
                }

                if (isExceluded) { continue; }

                if (detail.Type.Trim().ToUpper() == "ReplaceEnd".ToUpper())
                {
                    if (result.EndsWith(detail.SourceText))
                    {
                        result = result.Replace(detail.SourceText, detail.DestText);
                    }
                }
                else if (detail.Type.Trim().ToUpper() == "Remove".ToUpper())
                {
                    result = UpdateStringWithCondition(detail.SourceText, result);
                }
                else
                {
                    result = result.Replace(detail.SourceText, detail.DestText);
                }
            }
            
            return result;
        }

        private static string UpdateStringWithCondition(string removeString, string sourceValue)
        {
            if (sourceValue.IndexOf(removeString) >= 0)
            {
                string nextChar = sourceValue.Substring(sourceValue.IndexOf(removeString) + removeString.Length, 1);
                int value;
                if (!(int.TryParse(nextChar, out value)))
                {
                    sourceValue = sourceValue.Replace(removeString, ", ");
                }
            }
            return sourceValue;
        }
    }
}
